<?php
/**
 * User: Do
 * Date: 7/4/14
 * Time: 11:33 AM
 */
class Model_management extends Model{
    function __construct(){
        parent::__construct();
    }

    public function getCampaigns(){
        try{
            $sth = $this->db->prepare("select * from campaign");
            $sth->execute();
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getCampaignById($cid){
        try{
            $sth = $this->db->prepare("select * from campaign where cid=:cid");
            $sth->execute(array(
                ':cid' => $cid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function addCampaign($uid, $campaign_title, $campaign_short_description, $campaign_long_description, $campaign_url, $campaign_posted_time, $campaign_start_time, $campaign_end_time, $campaign_type, $campaign_gender, $campaign_age, $campaign_location, $campaign_housesize){
        try{
            $sth = $this->db->prepare("insert into campaign(`uid`, `campaign_title`, `campaign_short_description`, `campaign_long_description`, `campaign_url`, `campaign_posted_time`, `campaign_start_time`, `campaign_end_time`, `campaign_type`, `campaign_gender`, `campaign_age`, `campaign_location`, `campaign_housesize`)
            values(:uid, :campaign_title, :campaign_short_description, :campaign_long_description, :campaign_url, :campaign_posted_time, :campaign_start_time, :campaign_end_time, :campaign_type, :campaign_gender, :campaign_age, :campaign_location, :campaign_housesize)");
            $sth->execute(array(
                ':uid' => $uid,
                ':campaign_title' => $campaign_title,
                ':campaign_short_description' => $campaign_short_description,
                ':campaign_long_description' => $campaign_long_description,
                ':campaign_url' => $campaign_url,
                ':campaign_posted_time' => $campaign_posted_time,
                ':campaign_start_time' => $campaign_start_time,
                ':campaign_end_time' => $campaign_end_time,
                ':campaign_type' => $campaign_type,
                ':campaign_gender' => $campaign_gender,
                ':campaign_age' => $campaign_age,
                ':campaign_location' => $campaign_location,
                ':campaign_housesize' => $campaign_housesize
            ));
            return $this->db->lastInsertId();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getBuzzer($sql){
        try{
            $sth = $this->db->prepare("select uid, username, buzzscore from users where ".$sql."");
            $sth->execute();
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    //Survey
    public function getSurvey(){
        try{
            $sth = $this->db->prepare("select * from survey");
            $sth->execute();
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getSurveyById($sid){
        try{
            $sth = $this->db->prepare("select * from survey where sid = :sid");
            $sth->execute(array(
                ':sid' => $sid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getSurveyChild(){
        try{
            $sth = $this->db->prepare("select * from survey_child inner join survey on survey_child.sid = survey.sid order by survey_child_created desc");
            $sth->execute();
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getSurveyChildById($scid){
        try{
            $sth = $this->db->prepare("select * from survey_child where scid = :scid");
            $sth->execute(array(
                ':scid' => $scid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getSurveyChildBySid($sid){
        try{
            $sth = $this->db->prepare("select * from survey_child where sid = :sid order by survey_child_position asc");
            $sth->execute(array(
                ':sid' => $sid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function addSurveyChild($sid, $survey_child_name, $survey_child_description, $survey_child_created, $survey_child_modified, $survey_child_status){
        try{
            $sth = $this->db->prepare("insert into survey_child(`sid`, `survey_child_name`, `survey_child_description`, `survey_child_created`, `survey_child_modified`, `survey_child_status`)
            values(:sid, :survey_child_name, :survey_child_description, :survey_child_created, :survey_child_modified, :survey_child_status)");
            $sth->execute(array(
                ':sid' => $sid,
                ':survey_child_name' => $survey_child_name,
                ':survey_child_description' => $survey_child_description,
                ':survey_child_created' => $survey_child_created,
                ':survey_child_modified' => $survey_child_modified,
                ':survey_child_status' => $survey_child_status
            ));
            return $this->db->lastInsertId();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function updateSurveyChildPosition($scid, $position){
        try{
            $sth = $this->db->prepare("update survey_child set survey_child_position =:position where scid=:scid");
            $sth->execute(array(
                ':scid' => $scid,
                ':position' => $position
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function removeSurveyChildById($scid){
        $sth = $this->db->prepare("delete from survey_child where scid=:scid");
        $sth->execute(array(
            ':scid' => $scid
        ));
    }

    //Question
    public function getQuestionById($qid){
        try{
            $sth = $this->db->prepare("select * from question where qid = :qid");
            $sth->execute(array(
                ':qid' => $qid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getQuestionByScid($scid){
        try{
            $sth = $this->db->prepare("select * from question where scid = :scid order by question_position asc");
            $sth->execute(array(
                ':scid' => $scid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getQuestionChildById($qcid){
        try{
            $sth = $this->db->prepare("select * from question_child where qcid = :qcid");
            $sth->execute(array(
                ':qcid' => $qcid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getQuestionChildByQid($qid){
        try{
            $sth = $this->db->prepare("select * from question_child where qid = :qid");
            $sth->execute(array(
                ':qid' => $qid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getQuestionParentBySid($sid){
        try{
            $sth = $this->db->prepare("select * from question where parent_qid = 0 and scid = :scid order by question_position desc");
            $sth->execute(array(
                ':scid' => $sid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getQuestionParentBySidAsc($sid){
        try{
            $sth = $this->db->prepare("select * from question where parent_qid = 0 and scid = :scid order by question_position asc");
            $sth->execute(array(
                ':scid' => $sid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getQuestionByParentId($parent){
        try{
            $sth = $this->db->prepare("select * from question where parent_qid=:parent_qid order by question_position asc");
            $sth->execute(array(
                ':parent_qid' => $parent
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function addQuestion($parent_qid, $scid, $question_type, $question_name, $question_position, $question_relevance){
        try{
            $sth = $this->db->prepare("insert into question(parent_qid, scid, question_type, question_name, question_position, question_relevance)
            values( :parent_qid, :scid, :question_type, :question_name, :question_position, :question_relevance)");
            $sth->execute(array(
                ':parent_qid' => $parent_qid,
                ':scid' => $scid,
                ':question_type' => $question_type,
                ':question_name' => $question_name,
                ':question_position' => $question_position,
                ':question_relevance' => $question_relevance
            ));
            return $this->db->lastInsertId();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function addQuestionChild($qid, $question_child_name, $question_child_position){
        try{
            $sth = $this->db->prepare("insert into question_child(qid, question_child_name, question_child_position)
            values( :qid, :question_child_name, :question_child_position)");
            $sth->execute(array(
                ':qid' => $qid,
                ':question_child_name' => $question_child_name,
                ':question_child_position' => $question_child_position
            ));
            return $this->db->lastInsertId();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function addQuestionSingle($parent_qid, $scid, $question_type, $question_name, $question_position, $question_relevance){
        try{
            $sth = $this->db->prepare("insert into question(parent_qid, scid, question_type, question_name, question_position, question_relevance)
            values( :parent_qid, :scid, :question_type, :question_name, :question_position, :question_relevance)");
            $sth->execute(array(
                ':parent_qid' => $parent_qid,
                ':scid' => $scid,
                ':question_type' => $question_type,
                ':question_name' => $question_name,
                ':question_position' => $question_position,
                ':question_relevance' => $question_relevance
            ));
            return $this->db->lastInsertId();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function addQuestionMultiple(){

    }

    public function addQuestionTable(){

    }

    public function removeQuestionById(){

    }

    public function removeQuestionChildById(){

    }

    public function removeQuestionByScid($scid){
        $sth = $this->db->prepare("delete from question where scid=:scid");
        $sth->execute(array(
            ':scid' => $scid
        ));
        return true;
    }

    public function removeQuestionChildByQid($qid){
        $sth = $this->db->prepare("delete from question_child where qid=:qid");
        $sth->execute(array(
            ':qid' => $qid
        ));
        return true;
    }

    //Answers
    public function getAnsweredByUsid($usid){
        $sth = $this->db->prepare("select * from answered where usid=:usid");
        $sth->execute(array(
            ':usid' => $usid
        ));
        return $sth->fetchAll();
    }

    //Attributes
    public function getAttributeById($atid){
        $sth = $this->db->prepare("select * from attributes where atid=:atid");
        $sth->execute(array(
            ':atid' => $atid
        ));
        return $sth->fetchAll();
    }

    public function getAttributeTag(){
        $sth = $this->db->prepare("select * from attributes where attr_tag=1");
        $sth->execute();
        return $sth->fetchAll();
    }

    public function getAttributeByParent($parent){
        $sth = $this->db->prepare("select * from attributes where attr_parent=:attr_parent");
        $sth->execute(array(
            ':attr_parent' => $parent
        ));
        return $sth->fetchAll();
    }

    public function addAttribute($attr_name, $attr_position, $attr_parent, $attr_tag, $attr_type){
        try{
            $sth = $this->db->prepare("insert into attributes(attr_name, attr_position, attr_parent, attr_tag, attr_type)
            values(:attr_name, :attr_position, :attr_parent, :attr_tag, :attr_type)");
            $sth->execute(array(
                ':attr_name' => $attr_name,
                ':attr_position' => $attr_position,
                ':attr_parent' => $attr_parent,
                ':attr_tag' => $attr_tag,
                ':attr_type' => $attr_type,
            ));
            return $this->db->lastInsertId();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getQuestionAttributeByQid($qid){
        try{
            $sth = $this->db->prepare("select atid from question_attribute where qid=:qid");
            $sth->execute(array(
                ':qid' => $qid,
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function addQuestionAttribute($qid, $atid){
        try{
            $sth = $this->db->prepare("insert into question_attribute(qid, atid)
            values(:qid, :atid)");
            $sth->execute(array(
                ':qid' => $qid,
                ':atid' => $atid
            ));
            return $this->db->lastInsertId();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function updateAttributeName($atid, $name){
        $sth = $this->db->prepare("update attributes set attr_name=:attr_name where atid=:atid");
        $sth->execute(array(
            ':atid' => $atid,
            ':attr_name' => $name
        ));
        return true;
    }

    public function deleteAttribute($atid){
        $sth = $this->db->prepare("delete from attributes where atid=:atid");
        $sth->execute(array(
            ':atid' => $atid
        ));
        return true;
    }

    //User survey
    public function getUserSurveyByScid($scid){
        $sth = $this->db->prepare("select * from user_survey where scid=:scid");
        $sth->execute(array(
            ':scid' => $scid
        ));
        return true;
    }

    public function getQuestionAnswered($qid, $answered){
        $sth = $this->db->prepare("select a.qid, answered_id, count(answered_id) as number from (user_survey u inner join answered a on u.usid = a.usid) inner join question q on q.qid = a.qid where a.qid =:qid and answered_id =:answered group by a.qid");
        $sth->execute(array(
            ':qid' => $qid,
            ':answered' => $answered
        ));
        return $sth->fetchAll();
    }

    public function getQuestionAnsweredMultiChoose($qid, $answered){
        $sth = $this->db->prepare("select a.qid, answered_id, count(answered_id) as number from (user_survey u inner join answered a on u.usid = a.usid) inner join question q on q.qid = a.qid where a.question_parent =:qid and answered_id =:answered group by a.qid");
        $sth->execute(array(
            ':qid' => $qid,
            ':answered' => $answered
        ));
        return $sth->fetchAll();
    }

    public function getQuestionAnsweredTable($qid, $row, $column){
        $sth = $this->db->prepare("select a.qid, answered_id, count(answered_id) as number from answered a where a.question_parent = :qid and a.qid =:ansRow and answered_id =:ansColumn group by question_parent");
        $sth->execute(array(
            ':qid' => $qid,
            ':ansRow' => $row,
            ':ansColumn' => $column
        ));
        return $sth->fetchAll();
    }

    public function getNumberAnsweredOfQuestion($qid){
        $sth = $this->db->prepare("select count(usid) as number from answered where qid = :qid");
        $sth->execute(array(
            ':qid' => $qid
        ));
        return $sth->fetchAll();
    }


    //Images
    public function getImageById($mid){
        $sth = $this->db->prepare("select * from media where mid=:mid");
        $sth->execute(array(
            ':mid' => $mid
        ));
        return $sth->fetchAll();
    }

    public function addImages($uid, $cid, $cmid, $media_position, $media_type, $media_name, $media_title, $media_upload_time, $media_caption, $media_alt, $media_description){
        try{
            $sth = $this->db->prepare("insert into media(uid, cid, cmid, media_position, media_type, media_name, media_title, media_upload_time, media_caption, media_alt, media_description)
            values( :uid, :cid, :cmid, :media_position, :media_type, :media_name, :media_title, :media_upload_time, :media_caption, :media_alt, :media_description)");
            $sth->execute(array(
                ':uid' => $uid,
                ':cid' => $cid,
                ':cmid' => $cmid,
                ':media_position' => $media_position,
                ':media_type' => $media_type,
                ':media_name' => $media_name,
                ':media_title' => $media_title,
                ':media_upload_time' => $media_upload_time,
                ':media_caption' => $media_caption,
                ':media_alt' => $media_alt,
                ':media_description' => $media_description
            ));
            return $this->db->lastInsertId();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function updateImageCidById($mid, $cid){
        $sth = $this->db->prepare("update media set cid=:cid where mid=:mid");
        $sth->execute(array(
            ':mid' => $mid,
            ':cid' => $cid
        ));
        return true;
    }

    public function updateImageCmitById($mid, $cmid){
        $sth = $this->db->prepare("update media set cmid=:cmid where mid=:mid");
        $sth->execute(array(
            ':mid' => $mid,
            ':cmid' => $cmid
        ));
        return true;
    }

    public function removeImage($mid){
        $sth = $this->db->prepare("delete from media where mid=:mid");
        $sth->execute(array(
            ':mid' => $mid
        ));
        return true;
    }

    //Anything
    public function getProvince(){
        $sth = $this->db->prepare("select * from province");
        $sth->execute();
        return $sth->fetchAll();
    }


}